<?php
/**********************************************************************************
WIKINDX: Bibliographic Management system.
Copyright (C)

This program is free software; you can redistribute it and/or modify it under the terms
of the GNU General Public License as published by the Free Software Foundation; either
version 2 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program;
if not, write to the
Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

The WIKINDX Team 2007
sirfragalot@users.sourceforge.net
**********************************************************************************/
/*****
*	STATEMENTS class
*
*	Some default SQL statements/clauses.
*
*	$Header: /cvsroot/wikindx/wikindx3/core/sql/STATEMENTS.php,v 1.29 2007/07/28 19:04:09 sirfragalot Exp $
*****/
class STATEMENTS
{
// Constructor
	function STATEMENTS($db)
	{
		$this->db = $db;
		$this->ascDesc = 'ASC'; // default
	}
// All tables in database pertaining to resources used for searching and listing
	function allResourceTables()
	{
		return array("WKX_resource", "WKX_resource_category", "WKX_resource_keyword", 
			"WKX_collection", "WKX_publisher", "WKX_resource_musing", "WKX_resource_musing_text", 
			"WKX_resource_note", "WKX_resource_quote", "WKX_resource_quote_text",
			"WKX_resource_paraphrase", "WKX_resource_paraphrase_text",
			"WKX_resource_misc", "WKX_resource_year", "WKX_resource_page", "WKX_resource_summary",
			"WKX_resource_timestamp", "WKX_creator", "WKX_resource_creator", "WKX_creator_institution","WKX_institution");
	}
// tables whose id field is the same as WKX_resource.id and not containing bibliographic information
	function idIsIdTables()
	{
		return array("WKX_resource_category", "WKX_resource_keyword", "WKX_resource_note",
			"WKX_resource_timestamp", "WKX_resource_cite","WKX_creator_institution");
	}
// tables whose id field is the same as WKX_resource.id but only those containing bibliographic information
	function idIsIdBibTables()
	{
		return array("WKX_resource_misc", "WKX_resource_year", "WKX_resource_page", 
			"WKX_resource_creator", "WKX_resource_summary", "WKX_resource_note");
	}
// tables whose resourceId field is the same as WKX_resource.id
	function idIsResourceIdTables()
	{
		return array("WKX_resource_musing", "WKX_resource_quote", "WKX_resource_paraphrase","WKX_creator");
	}
// Table fields to be used as the basis for WIKINDX list queries
	function listFields($method = FALSE)
	{
// fields from WKX_resource:
		$array = array(
			array("WKX_resource.id" => "resourceId"), "type", "title", "shortTitle", "doi", 
			"subtitle", "noSort", "url", "isbn", "field1", "field2", "field3", "field4", "field5", 
			"field6", "field7", "field8", "field9", "transTitle", "transSubtitle", "transNoSort", "bibtexKey", 
// fields from WKX_resource_misc:
			"collection", "publisher", "miscField1", "miscField2", "miscField3", "miscField4", 
			"miscField5", "miscField6", "tag", "addUserIdResource", "editUserIdResource", "accesses",
			"attachDownloads", "maturityIndex", "peerReviewed", 
// fields from WKX_resource_year:
			"year1", "year2", "year3", "year4",
// fields from WKX_resource_page:
			"pageStart", "pageEnd", 
// fields from WKX_resource_creator:
			"creator1",  "creator2", "creator3", "creator4", "creator5",
// fields from WKX_resource_summary:
			"quotes", "paraphrases", "musings", 
// fields from WKX_publisher:
			"publisherName", "publisherLocation", "publisherType", 
// fields from WKX_collection:
			"collectionTitle", "collectionTitleShort", "collectionType", "collectionString",
// fields from WKX_resource_timestamp:
			array("WKX_resource_timestamp.timestamp" => "timestamp"), 
			array("WKX_resource_timestamp.timestampAdd" => "timestampAdd")//, "surname", "firstname", "initials", "prefix",
// fields from WKX_creator_institution:
			//"institution1",  "institution2", "institution3", "institution4", "institution5"
		);
		if(($method == 'creator') || ($method == 'basket'))
			$array =  array_merge($array, array("surname"));
		return $array;
	}
// LEFT JOIN tables on WKX_resource for WIKINDX list queries.  Return a string.
// If $condition, this is for a user bibliography and $condition must appear before ORDER BY.
// If searching on title, remove '{', '}' and double and single quotes.
	function listJoin($method = FALSE, $condition = FALSE)
	{
		foreach($this->idIsIdBibTables() as $table)
			$join[] = "LEFT JOIN " . $this->db->formatTable($table) . " ON " . 
			$this->db->formatfield("WKX_resource.id") . "=" . $this->db->formatfield("$table.id");
		$join[] = "LEFT JOIN " . $this->db->formatTable('WKX_publisher') . " ON " . 
			$this->db->formatField('publisher') . "=" . $this->db->formatfield("WKX_publisher.id");
		$join[] = "LEFT JOIN " . $this->db->formatTable('WKX_collection') . 
			" ON " . $this->db->formatField('collection') . "=" . $this->db->formatfield("WKX_collection.id");
		$join[] = "LEFT JOIN " . $this->db->formatTable('WKX_resource_timestamp') . 
			" ON " . $this->db->formatfield("WKX_resource_timestamp.id") . "=" . 
			$this->db->formatfield("WKX_resource.id");
		if($method == 'title')
			$join[] = $condition . " ORDER BY " . 
				"REPLACE(REPLACE(REPLACE(REPLACE(" . $this->db->tidyInputClause('title') . ", '{', ''), '}', '') 
				, '\"', ''), '\'', '')" . 
				" " . $this->ascDesc . ", " . 
				"COALESCE(" . 
				$this->db->tidyInputClause('year1') . ", " .
				$this->db->tidyInputClause('year2') . ", " . 
				$this->db->tidyInputClause('year3') . 
				")"; 
		else if(($method == 'creator') || ($method == 'basket'))
			$join[] = "LEFT JOIN " . $this->db->formatTable('WKX_creator') . " ON " . 
			$this->db->formatfield("WKX_creator.id") . "=COALESCE(COALESCE(" . 
			$this->db->formatField('creator1') . "," . 
			$this->db->formatField('creator2') . "))" . 
/*			$condition . " ORDER BY " . $this->db->tidyInputClause('surname') . 
				" " . $this->ascDesc . ", " . 
				$this->db->tidyInputClause('firstname') .  
				", COALESCE(" . 
				$this->db->tidyInputClause('year1') . ", " .
				$this->db->tidyInputClause('year2') . ", " . 
				$this->db->tidyInputClause('year3') . 
				")";
*/
// New ordering for v3.4.5 -- correctly orders on title if no surname
			$condition . " ORDER BY " . 
				"IF(" . $this->db->tidyInputClause('surname') . 
				" IS NOT NULL, " . 
					$this->db->tidyInputClause('surname') . 
				", REPLACE(REPLACE(REPLACE(REPLACE(" . $this->db->tidyInputClause('title') . ", '{', ''), '}', 
				'') , '\"', ''), '\'', ''))" . 
				" " . $this->ascDesc . 
				", " . $this->db->tidyInputClause('firstname') . 
				", COALESCE(" . 
				$this->db->tidyInputClause('year1') . ", " .
				$this->db->tidyInputClause('year2') . ", " . 
				$this->db->tidyInputClause('year3') . 
				")";
		else if($method == 'publisher')
			$join[] = $condition . " ORDER BY " . $this->db->tidyInputClause('publisherName') . 
				" " . $this->ascDesc . ", " . 
				"COALESCE(" . 
				$this->db->tidyInputClause('year1') . ", " .
				$this->db->tidyInputClause('year2') . ", " . 
				$this->db->tidyInputClause('year3') . 
				")"; 
		else if($method == 'timestamp')
		{
			$join[] = $condition . " ORDER BY " . $this->db->tidyInputClause('timestamp') . 
				" " . $this->ascDesc . ", " . 
				"REPLACE(REPLACE(REPLACE(REPLACE(" . $this->db->tidyInputClause('title') . ", '{', ''), '}', '') 
				, '\"', ''), '\'', '')" . 
				", COALESCE(" . 
				$this->db->tidyInputClause('year1') . ", " .
				$this->db->tidyInputClause('year2') . ", " . 
				$this->db->tidyInputClause('year3') . 
				")"; 
		}
		else if($method == 'timestampAdd')
		{
			$join[] = $condition . " ORDER BY " . $this->db->tidyInputClause('timestampAdd') . 
				" " . $this->ascDesc . ", " . 
				"REPLACE(REPLACE(REPLACE(REPLACE(" . $this->db->tidyInputClause('title') . ", '{', ''), '}', '') 
				, '\"', ''), '\'', '')" . 
				", COALESCE(" . 
				$this->db->tidyInputClause('year1') . ", " .
				$this->db->tidyInputClause('year2') . ", " . 
				$this->db->tidyInputClause('year3') . 
				")"; 
		}
		else if($method == 'accesses')
		{
			$join[] = "LEFT JOIN " . $this->db->formatTable('WKX_creator') . " ON " . 
			$this->db->formatfield("WKX_creator.id") . "=COALESCE(COALESCE(" . 
			$this->db->formatField('creator1') . "," . 
			$this->db->formatField('creator2') . "))" . 
			$condition . " ORDER BY " . $this->db->tidyInputClause('accesses') . 
				" " . $this->ascDesc . ", " . 
				$this->db->tidyInputClause('surname') . 
				", COALESCE(" . 
				$this->db->tidyInputClause('year1') . ", " .
				$this->db->tidyInputClause('year2') . ", " . 
				$this->db->tidyInputClause('year3') . 
				")"; 
		}
		else if($method == 'maturityIndex')
		{
			$join[] = "LEFT JOIN " . $this->db->formatTable('WKX_creator') . " ON " . 
			$this->db->formatfield("WKX_creator.id") . "=COALESCE(COALESCE(" . 
			$this->db->formatField('creator1') . "," . 
			$this->db->formatField('creator2') . "))" . 
			$condition . " ORDER BY " . $this->db->tidyInputClause('maturityIndex') . 
				" " . $this->ascDesc . ", " . 
				$this->db->tidyInputClause('surname') . 
				", COALESCE(" . 
				$this->db->tidyInputClause('year1') . ", " .
				$this->db->tidyInputClause('year2') . ", " . 
				$this->db->tidyInputClause('year3') . 
				")"; 
		}
		else if($method == 'year')
			$join[] = $condition . " ORDER BY COALESCE(" . 
			$this->db->tidyInputClause('year1') . ", " .
			$this->db->tidyInputClause('year2') . ", " . 
			$this->db->tidyInputClause('year3') . 
			")" . " " . $this->ascDesc;
		return implode(" ", $join);
	}
// LEFT JOIN tables on WKX_resource for WIKINDX select resource queries.  Return a string.
	function selectJoin()
	{
		$tables = array_merge($this->idIsIdBibTables(), array("WKX_resource_category", "WKX_resource_keyword",
			"WKX_resource_abstract"));
		foreach($tables as $table)
			$join[] = "LEFT JOIN " . $this->db->formatTable($table) . " ON " . 
			$this->db->formatfield("WKX_resource.id") . "=" . $this->db->formatfield("$table.id");
		$join[] = "LEFT JOIN " . $this->db->formatTable('WKX_publisher') . 
			" ON " . $this->db->formatfield("WKX_resource_misc.publisher") . "=" . 
			$this->db->formatfield("WKX_publisher.id");
		$join[] = "LEFT JOIN " . $this->db->formatTable('WKX_collection') . 
			" ON " . $this->db->formatfield("WKX_resource_misc.collection") . "=" . 
			$this->db->formatfield("WKX_collection.id");
		$join[] = "LEFT JOIN " . $this->db->formatTable('WKX_resource_timestamp') . 
			" ON " . $this->db->formatfield("WKX_resource_timestamp.id") . "=" . 
			$this->db->formatfield("WKX_resource.id");
		$join[] = "LEFT JOIN " . $this->db->formatTable('WKX_creator') . " ON " . 
			$this->db->formatfield("WKX_creator.id") . "=COALESCE(COALESCE(" . 
			$this->db->formatfield("WKX_resource_creator.creator1") . ", " . 
			$this->db->formatfield("WKX_resource_creator.creator2") . "))";
		return implode(" ", $join);
	}
}
?>
